﻿using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;

namespace DAO
{
    public class TANGDAO
    {
        ///CỦA AI THÌ TỰ ĐÓNG KHUNG VÀO. TRÁNH XÓA NHẦM CODE
        /////////////////CỦA LINH///////////////////
        public List<TANGDTO> LayDSTang()
        {
            SqlConnection conn = DataProvider.ConnectDB();
            //Lấy danh sách tầng còn bàn trống
            SqlCommand command = new SqlCommand("LINH11_LAYDSTANGCONBANTRONG", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            SqlDataReader reader = command.ExecuteReader();
            List<TANGDTO> ds = new List<TANGDTO>();
            while (reader.Read())
            {
                TANGDTO TDTO = new TANGDTO();
                TDTO.TenTang = reader.GetString(1);
                TDTO.MaTang = reader.GetInt32(0);
                TDTO.TinhTrang = 0;//Tầng có khách còn bàn
                ds.Add(TDTO);
            }
            //Lấy danh sách tầng chưa có khách
            reader.Close();
            command = new SqlCommand("LINH13_LAYDSTANGCHUACOKHACH", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            reader = command.ExecuteReader();
            while (reader.Read())
            {
                TANGDTO TDTO = new TANGDTO();
                TDTO.TenTang = reader.GetString(1);
                TDTO.MaTang = reader.GetInt32(0);
                TDTO.TinhTrang = 0;//Có khách còn bàn
                if (ds.Contains(TDTO)) continue;
                TDTO.TinhTrang = -1;//Chưa có khách
                ds.Add(TDTO);
            }
            //Lấy danh sách tầng hết bàn 
            reader.Close();
            command = new SqlCommand("LINH15_LAYDSTANGHETBANTRONG", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            reader = command.ExecuteReader();
            while (reader.Read())
            {
                TANGDTO TDTO = new TANGDTO();
                TDTO.TenTang = reader.GetString(1);
                TDTO.MaTang = reader.GetInt32(0);
                TDTO.TinhTrang = -1;//Tầng chưa có khách
                if (ds.Contains(TDTO)) continue;
                TDTO.TinhTrang = 0;//Tầng có khách còn bàn
                if (ds.Contains(TDTO)) continue;
                TDTO.TinhTrang = 1;//Hết bàn trống
                ds.Add(TDTO);
            }
            conn.Close();
            return ds;
        }
        public List<TANGDTO> LayDSTang2(int n)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            //Lấy danh sách tầng có khách còn bàn
            SqlCommand command = new SqlCommand("LINH12_LAYDSTANGCONBANTRONG2", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.Add("@SoKhach", System.Data.SqlDbType.Int);
            command.Parameters["@SoKhach"].Value = n;

            SqlDataReader reader = command.ExecuteReader();
            List<TANGDTO> ds = new List<TANGDTO>();
            while (reader.Read())
            {
                TANGDTO TDTO = new TANGDTO();
                TDTO.TenTang = reader.GetString(1);
                TDTO.MaTang = reader.GetInt32(0);
                TDTO.TinhTrang = 0;//Có khách còn bàn
                ds.Add(TDTO);
            }
            //Lấy danh sách tầng chưa có khách
            reader.Close();
            command = new SqlCommand("LINH14_LAYDSTANGCHUACOKHACH2", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.Add("@SoKhach", System.Data.SqlDbType.Int);
            command.Parameters["@SoKhach"].Value = n;
            reader = command.ExecuteReader();
            while (reader.Read())
            {
                TANGDTO TDTO = new TANGDTO();

                TDTO.TenTang = reader.GetString(1);
                TDTO.MaTang = reader.GetInt32(0);
                TDTO.TinhTrang = 0;//Có khách còn bàn
                if (ds.Contains(TDTO)) continue;
                TDTO.TinhTrang = -1;//Chưa có khách
                ds.Add(TDTO);
            }
            //Lấy danh sách tầng hết bàn 
            reader.Close();
            command = new SqlCommand("LINH16_LAYDSTANGHETBANTRONG2", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.Add("@SoKhach", System.Data.SqlDbType.Int);
            command.Parameters["@SoKhach"].Value = n;
            reader = command.ExecuteReader();
            while (reader.Read())
            {
                TANGDTO TDTO = new TANGDTO();
                
                TDTO.TenTang = reader.GetString(1);
                TDTO.MaTang = reader.GetInt32(0);
                TDTO.TinhTrang = -1;//Chưa có khách
                if (ds.Contains(TDTO)) continue;
                TDTO.TinhTrang = 0;//Có khách còn bàn
                if (ds.Contains(TDTO)) continue;
                TDTO.TinhTrang = 1;//Hết bàn trống
                ds.Add(TDTO);
            }
            conn.Close();
            return ds;
        }
        ////////////////////////////////////////////
        public DataTable DanhSachTangCBO(TANGDTO TDTO)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("T23_LayDSTang", conn);
             SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(dt);

            conn.Close();
            return dt;
        }

        public DataTable DanhSachTang (TANGDTO TDTO)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("T3_TIMTANG", conn);
            command.Parameters.Add("@TenTang", System.Data.SqlDbType.NVarChar,50).Value = TDTO.TenTang;
            command.CommandType = System.Data.CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(dt);

            conn.Close();
            return dt;
        }
        public int ThemTang(TANGDTO TDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand("T6_THEMTANG", conn);
            cmd.Parameters.Add("@TenTang", System.Data.SqlDbType.NVarChar,50).Value = TDTO.TenTang;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq > 0)
            {
                return 1;
            }
            else { return 0; }
        }
        public int XoaTang(TANGDTO TDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand("T7_XOATANG", conn);
            cmd.Parameters.Add("@MaTang", System.Data.SqlDbType.Int).Value = TDTO.MaTang;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq > 0)
                return 1;
            else
                return 0;
        }

        public int SuaTang(TANGDTO TDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand("T5_SUATANG", conn);
            cmd.Parameters.Add("@TenTangCu", System.Data.SqlDbType.NVarChar,50).Value = TDTO.TenTangCu;
            cmd.Parameters.Add("@TenTang", System.Data.SqlDbType.NVarChar,50).Value = TDTO.TenTang;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            int kq = cmd.ExecuteNonQuery();
            cmd.ExecuteScalar();
            conn.Close();
            if (kq > 0)
                return 1;
            else
            return 0;
        }

        public DataTable DanhSachTangFull(TANGDTO TDTO)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("T2_TIMTANGALL", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(dt);

            conn.Close();
            return dt;
        }
        public DataTable QLTangXoa(TANGDTO TDTO)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("t15_QLXOATANG", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(dt);

            conn.Close();
            return dt;
        }
        public int PhucHoiTang(TANGDTO TDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand("T17_PHUCHOITANG", conn);
            cmd.Parameters.Add("@MaTang", System.Data.SqlDbType.Int).Value = TDTO.MaTang;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq > 0)
                return 1;
            else
                return 0;
        }
        public DataTable LOADTANG(TANGDTO TDTO)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("T1_LOADSUKIENTANG", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(dt);

            conn.Close();
            return dt;
        }
        public int XoaMatTang(TANGDTO TDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand("T21_XOAMATTANG", conn);
            cmd.Parameters.Add("@MaTang", System.Data.SqlDbType.Int).Value = TDTO.MaTang;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq > 0)
                return 1;
            else
                return 0;
        }
        public DataTable DSTANGXOA(TANGDTO TDTO)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("T8_LAYDSTANGXOA", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(dt);

            conn.Close();
            return dt;
        }

    }
}
        